/*
 * Copyright (C) 2012 Santiago Valdarrama
 * 
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at 
 * 
 * http://www.apache.org/licenses/LICENSE-2.0
 * 
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 * 
 */
package com.yuanmei.stockeyes.data;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

import com.yuanmei.stockeyes.R;

public class LonghornOpenHelper extends SQLiteOpenHelper {
    private static String DATABASE_NAME = "stkeyes_base.rdb";
    private static String LOG_TAG = "JKP_LonghornOpenHelper";
    
    
    //private final Context context;
    //private boolean databaseNeedsToBeCreated;
    //private boolean databaseNeedsToBeUpgraded;
    
    public LonghornOpenHelper(Context context) {
        super(context, DATABASE_NAME, null, context.getResources().getInteger(R.integer.databaseVersion));
        //this.context = context;
    }
    
    @Override
    public void onCreate(SQLiteDatabase db) {
        //this.databaseNeedsToBeCreated = true;
        initTables(db);
    }
    
    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        if (newVersion >=5){
            db.execSQL("DROP TABLE IF EXISTS stock_chart");
            db.execSQL("DROP TABLE IF EXISTS exchange");
            Log.i(LOG_TAG, "UPGRADE the userinfo data base from oldVersion="
                    + oldVersion + " to newVersion=" + newVersion);
        }
        if (!tabIsExist("watchlist")) {
            initTables(db);
            Log.i(LOG_TAG, "Init stock_base.rdb");
        }
    }
 
    private void initTables(SQLiteDatabase db){
        try {
             db.execSQL("DROP TABLE IF EXISTS stock_chart");
             db.execSQL("DROP TABLE IF EXISTS exchange");

             db.execSQL("DROP TABLE IF EXISTS stock");
             db.execSQL("CREATE TABLE IF NOT EXISTS stock  "
                 + "(stock_code TEXT, stock_name TEXT, stock_change_percentage FLOAT, "
                 + " stock_change FLOAT, stock_price FLOAT, stock_open FLOAT, stock_low FLOAT, "
                 + " stock_high FLOAT, stock_yestclose FLOAT, stock_volume FLOAT, "
                 + " stock_turnover FLOAT, stock_bid5 FLOAT, stock_bidvol5 FLOAT, stock_bid4 FLOAT, "
                 + " stock_bidvol4 FLOAT, stock_bid3 FLOAT, stock_bidvol3 FLOAT, stock_bid2 FLOAT, "
                 + " stock_bidvol2 FLOAT, stock_bid1 FLOAT, stock_bidvol1 FLOAT, stock_ask1 FLOAT,"
                 + " stock_askvol1 FLOAT, stock_ask2 FLOAT, stock_askvol2 FLOAT, stock_ask3 FLOAT,"
                 + " stock_askvol3 FLOAT, stock_ask4 FLOAT,  stock_askvol4 FLOAT, stock_ask5 FLOAT, "
                 + " stock_askvol5 FLOAT, stock_update TEXT)");

             db.execSQL("INSERT INTO stock (stock_code, stock_name) VALUES ('^sh000001', '上证指数')");
             db.execSQL("INSERT INTO stock (stock_code, stock_name) VALUES ('^sz399001', '深证成指')");
             db.execSQL("INSERT INTO stock (stock_code, stock_name) VALUES ('^sh000300', '沪深300')");
             
             
             db.execSQL("DROP TABLE IF EXISTS watchlist");
             db.execSQL("CREATE TABLE IF NOT EXISTS watchlist  "
                    + "(watchlist_code TEXT NOT NULL, "
                    + " watchlist_order INTEGER NOT NULL)");
             db.execSQL("INSERT INTO watchlist (watchlist_code, watchlist_order) VALUES ('^sh000001', 1)");
             db.execSQL("INSERT INTO watchlist (watchlist_code, watchlist_order) VALUES ('^sz399001', 2)");
             db.execSQL("INSERT INTO watchlist (watchlist_code, watchlist_order) VALUES ('^sh000300', 3)");
             
             db.execSQL("DROP TABLE IF EXISTS stock_timedata");
             db.execSQL("CREATE TABLE IF NOT EXISTS stock_timedata  "
                    + "(rec_code TEXT NOT NULL, "
                    + " rec_time BIGINT NOT NULL, "
                    + " rec_price FLOAT NOT NULL, "
                    + " rec_vol FLOAT, "
                    + " rec_turnover FLOAT)");
             
        } catch (Exception e) {
            Log.e(LOG_TAG, "CREATE the stkeyes_base.rdb Exception=" + e);
        }
        Log.i(LOG_TAG, "CREATE the stkeyes_base.rdb at success");
    }
 
    public boolean tabIsExist(String tabName) {
        boolean result = false;
        if (tabName == null) {
            return false;
        }
        SQLiteDatabase db = null;
        Cursor cursor = null;
        try {
            db = this.getReadableDatabase();
            String sql = "SELECT COUNT(*) AS c FREOM sqlite_master "
                    + "WHERE type ='table' AND name ='" + tabName.trim() + "'";
            cursor = db.rawQuery(sql, null);
            if (cursor.moveToNext()) {
                int count = cursor.getInt(0);
                if (count > 0) {
                    result = true;
                }
            }

        } catch (Exception e) {
            // TODO: handle exception
        }
        return result;
    }

}
